Public Const Moi = "Mr KOUHLANI"
Dim DerniereLigne, DerniereCol, i As Byte
Dim Matiere, Source As String
Dim Massar As Workbook

Sub GetData2Appreciations()

    On Error Resume Next
    
    With Range("D12")
        If .Value <> " Programm par :  Mr KOUHLANI Abdellatif" Then .Value = " Programm par :  Mr KOUHLANI Abdellatif"
        If .RowHeight <> 20 Then .RowHeight = 20
        If .ColumnWidth <> 45 Then .ColumnWidth = 45
        If .Interior.Color <> 16179966 Then .Interior.Color = 16179966
        If .Font.Size <> 12 Then .Font.Size = 12
        If .Font.Color <> vbBlack Then .Font.Color = vbBlack
        If .Font.Name <> "Calibri" Then .Font.Name = "Calibri"
    End With
        
    NomFichier = Application.GetOpenFilename("Fichier Excel (*.xlsx), ", , _
    "Atteindre le fichier Massar contenant la liste des lves       -  Anaspace  -")
    
    If NomFichier = False Then
        MsgBox "Vous avez cliquez sur ""Annuler"" ou ""Fermer"" !", , Moi
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Set Massar = Workbooks.Open(NomFichier)
    Source = Massar.Name
    
    If Sheets(1).Range("B16") <> "ID" Then
        Massar.Close SaveChanges:=False
        MsgBox "le fichier que vous avez choisi n'est pas un fichier ""Massar"" :  (" _
        & Source & ")" & vbCrLf & vbCrLf & "Veuillez ressayer !", vbInformation, Moi
        Range("A1").Select
        Exit Sub
    End If
    
    Sheets(1).Activate
    DerniereCol = ActiveWorkbook.Sheets(1).Range("BB15").End(xlToLeft).Column
    DerniereLigne = ActiveWorkbook.Sheets(1).Range("C200").End(xlUp).Row
    Matiere = ActiveWorkbook.Sheets(1).Range("K5").Value

    For i = 1 To (DerniereCol - 7) / 2
        ActiveWorkbook.Sheets(1).Range(Cells(18, 5 + (i * 2)), Cells(DerniereLigne, 5 + (i * 2))).Copy
        ThisWorkbook.Sheets("Apprciations").Cells(18, 12 + i).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next i
    
    ThisWorkbook.Sheets("Apprciations").Calculate
    
    If Matiere = "#0012#" Then
        ThisWorkbook.Sheets("Apprciations").Range("France").Copy
    Else
        ThisWorkbook.Sheets("Apprciations").Range("Arabe").Copy
    End If
    
    ActiveWorkbook.Sheets(1).Cells(18, DerniereCol).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWorkbook.Sheets(1).Range("A3").Select
    Massar.Close SaveChanges:=True
    ThisWorkbook.Sheets("Apprciations").Activate
    Application.CutCopyMode = False
    Range("ZoneTemp").ClearContents
    Cells(12, 4).Select
    MsgBox "Flicitations !" & vbLf & vbLf & "les apprciations ont t insres avec succs", , Moi
    Application.ScreenUpdating = True
    
End Sub

Sub Hello()
    MsgBox Moi, , Now()
End Sub
